package com.jysz.certificate.persistence.dao.cm;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;

import com.jysz.certificate.persistence.dao.AbstractBaseDao;
import com.jysz.certificate.persistence.enums.CertificateStatus;
import com.jysz.certificate.persistence.enums.CertificationType;
import com.jysz.certificate.persistence.model.cm.Certificate;
import com.jysz.certificate.persistence.model.cm.Certification;
import com.jysz.certificate.utils.DateUtil;


@Repository
public class CertificationCoreDao extends AbstractBaseDao<Certification, Long> {
	
	

	//查找店铺下是否有合格证
	public boolean findCertificationByShop(Long shopId){
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.eq("shop.id", shopId));
		criteria.add(Restrictions.ne("state", CertificationType.RENEWOUT));
		criteria.add(Restrictions.ne("state", CertificationType.RELEASEED));
		List<Certification> list = criteria.list();
		if(list!=null&&list.size()>0){
			return false;
		} 
		return true;
	}
	
	public Certification certificationChassisKey(String chassis){
		List<Certification> list=this.findBy("chassis", chassis);
		if(list!=null&&list.size()>0){
			return list.get(0);
		}
		return null;
	}
	
	public int cheeckStoInteger(Long shopId, Long fId){//获取当月释放数
		Criteria c = this.getSession().createCriteria(Certification.class);
		c.add(Restrictions.eq(Certification.STATE,CertificationType.RELEASEED));
		c.add(Restrictions.between("leaveDate", DateUtil.getFirstdayLastdayMonthOne(new Date()), DateUtil.getFirstdayLastdayMonth(new Date())));
		c.createCriteria(Certification.SHOP).add(Restrictions.eq("id",shopId));
		c.createCriteria(Certification.COLUMN_FINANCIAL).add(Restrictions.eq("id",fId));
		List<Certification> list=c.list();
		int idex=list.size();
		return idex;
		
	}
	public int cheeckStoQuarterInteger(Long shopId, Long fId,Date date1,Date date2){//获取季度释放数
		Criteria c = this.getSession().createCriteria(Certification.class);
		c.add(Restrictions.eq(Certification.STATE,CertificationType.RELEASEED));
		c.add(Restrictions.between("leaveDate", date1, date2));
		c.createCriteria(Certification.SHOP).add(Restrictions.eq("id",shopId));
		c.createCriteria(Certification.COLUMN_FINANCIAL).add(Restrictions.eq("id",fId));
		List<Certification> list=c.list();
		int idex=list.size();
		return idex;
		
	}
	
	public boolean getCertifition(Certificate cer){
		Criteria c = this.createCriteria();
		c.add(Restrictions.ne("id", cer.getCertification().getId()));
		c.add(Restrictions.disjunction().add(Restrictions.eq("certificationNum", 
				cer.getCertificationNum())).add(Restrictions.eq("chassis", cer.getChassis())));
		List list = c.list();
		if(list!=null&&list.size()>0){
			return false;
		}
		return true;
	}
	/**
	 * 返回暂存 ，在途，待审核，未通过 的所有合格证
	 * @param chassises 
	 * @return
	 */
	public Map<String, Certification> certificationMap(List<String> chassises){
		Map<String, Certification> map = new HashMap<String, Certification>();
		Criteria criteria = this.createCriteria();
		
		if(chassises != null && chassises.size() > 0){
			criteria.add(Restrictions.in("chassis", chassises));
		}else{
			criteria.add(Restrictions.sqlRestriction("1 != 1"));
		}
		
		criteria.add(
				Restrictions.or(
				Restrictions.or(Restrictions.eq("state", CertificationType.UNFINISHED), Restrictions.eq("state", CertificationType.ONORDER)),
				Restrictions.or(Restrictions.eq("state", CertificationType.APPROVED),Restrictions.eq("state", CertificationType.FILL))));
		List<Certification> certifications = criteria.list();
		if(certifications.size() > 0){
			for(Certification certification : certifications){
				if(!map.containsKey(certification.getChassis())){
					map.put(certification.getChassis(), certification);
				}
			}
		}
		
		return map;
	}
	/**
	 * 返回 非 暂存 ，在途，待审核，未通过 的所有合格证
	 * @param chassises 
	 * @param chassis
	 * @return
	 */
	public Map<String, Certification> certificationMapNot(List<String> chassises){
		Map<String, Certification> map= new HashMap<String, Certification>();
		Criteria criteria = this.createCriteria();
		
		if(chassises != null && chassises.size() > 0){
			criteria.add(Restrictions.in("chassis", chassises));
		}else{
			criteria.add(Restrictions.sqlRestriction("1 != 1"));
		}
		
		criteria.add(
				Restrictions.or(
				Restrictions.or(Restrictions.eq("state", CertificationType.INSTOCK), Restrictions.eq("state", CertificationType.RELEASEED)),
				Restrictions.or(Restrictions.eq("state", CertificationType.RENEWPASS),Restrictions.eq("state", CertificationType.RENEWOUT))));
		List<Certification> certifications = criteria.list();
		if(certifications.size()>0){
			for(Certification certification : certifications){
				if(!map.containsKey(certification.getChassis())){
					map.put(certification.getChassis(), certification);
				}
			}
			return map;
		}else{
			return null ;
		}
	}
	
	/**
	 * 返回 非 暂存 ，在途，待审核，未通过 的所有合格证
	 * @param certificationNums 
	 * @param chassis
	 * @return
	 */
	public Map<String, Certification> certificationNumMapNot(List<String> certificationNums){
		Map<String, Certification> map = new HashMap<String, Certification>();
		Criteria criteria = this.createCriteria();
		
		if(certificationNums != null && certificationNums.size() > 0){
			criteria.add(Restrictions.in("certificationNum", certificationNums));
		}else{
			criteria.add(Restrictions.sqlRestriction("1 != 1"));
		}
		
		List<Certification> certifications = criteria.list();
		if(certifications.size() > 0){
			for(Certification certification : certifications){
				if(!map.containsKey(certification.getCertificationNum())){
					map.put(certification.getCertificationNum(), certification);
				}
			}
		}
		
		return map;
	}
	
	/**
	 * 获得指定店铺、金融机构、月份释放的合格证数量
	 * @author xuxiaolong
	 * @param shopId 店铺ID
	 * @param finId  金融机构
	 * @param month  月份 yyyy-MM
	 * @return
	 */
	public int getReleaseCount(Long shopId, Long finId,String month){
		
		Criteria c = this.getSession().createCriteria(Certification.class);
		c.add(Restrictions.eq(Certification.STATE,CertificationType.RELEASEED));
		Date d=DateUtil.parseDate(month, "yyyy-MM");
		c.add(Restrictions.between("leaveDate", DateUtil.getFirstdayLastdayMonthOne(d), DateUtil.getFirstdayLastdayMonth(d)));
		c.createCriteria(Certification.SHOP).add(Restrictions.eq("id",shopId));
		c.createCriteria(Certification.COLUMN_FINANCIAL).add(Restrictions.eq("id",finId));
		List list=c.list();
		if(list!=null)
			return list.size();
		return 0;
		
	}
	
	/**
	 * 融资车总台数合计同比 
	 * @author xuxiaolong
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<Integer,Integer> findCarTotal(Date startDate,Date endDate){
		
		StringBuilder sb = new StringBuilder("SELECT DATE_FORMAT(cf._warehouseDate, '%Y') AS yy , COUNT(*) AS counts FROM cm_certification_  cf ");
		sb.append(" WHERE cf._warehouseDate >=:startDate ");
		sb.append(" AND cf._warehouseDate <=:endDate ");
		sb.append(" AND cf._cmImporting!=2");
		sb.append(" GROUP BY  yy");

		SQLQuery q=getSession().createSQLQuery(sb.toString());
		q.addScalar("yy", IntegerType.INSTANCE);
		q.addScalar("counts", IntegerType.INSTANCE);
		q.setDate("startDate", startDate);
		q.setDate("endDate", endDate);
		List<Object[]> list=q.list();
		
		Map<Integer,Integer> rus=new HashMap<Integer, Integer>();
		for(Object[] ary:list){
			if(ary.length==2){
				rus.put((Integer)ary[0], (Integer)ary[1]);
			}
		}
		return rus;
	}
	
	/**
	 * 各银行融资车台数
	 * @author xuxiaolong
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> findCarFin(Date startDate,Date endDate){
		
		StringBuilder sb = new StringBuilder("SELECT DATE_FORMAT(cf._warehouseDate, '%Y') AS yy  , f._brief AS fin ,COUNT(*) AS counts  ");
		sb.append(" FROM cm_certification_  cf ,sys_financial_ f ");
		sb.append(" WHERE cf._financial_id=f.id  ");
		sb.append(" AND cf._warehouseDate >=:startDate ");
		sb.append(" AND cf._warehouseDate <=:endDate ");
		sb.append(" AND cf._cmImporting!=2");
		sb.append(" GROUP BY cf._financial_id,yy  ");

		SQLQuery q=getSession().createSQLQuery(sb.toString());
		q.addScalar("yy", IntegerType.INSTANCE);
		q.addScalar("fin", StringType.INSTANCE);
		q.addScalar("counts", IntegerType.INSTANCE);
		q.setDate("startDate", startDate);
		q.setDate("endDate", endDate);
		List<Object[]> list=q.list();
		
		Set<String> bankSet=new HashSet<String>();
		for(Object tmp[]:list){
			bankSet.add(tmp[1].toString());
		}
		Map<Integer,Map<String,Integer>> map=new HashMap<Integer, Map<String,Integer>>();
		Map<String,Integer> in=null;
		for(Object tmp[]:list){
			Integer i=(Integer)tmp[0];
			if(map.containsKey(i)){
				map.get(i).put(tmp[1].toString(), (Integer)tmp[2]);
			}else{
				in=new HashMap<String,Integer>();
				in.put(tmp[1].toString(), (Integer)tmp[2]);
				map.put(i, in);
			}
		}
		
		List<Object []> rus=new ArrayList<Object[]>();
		Object [] objs=null;
		for(Integer y:map.keySet()){
			in=map.get(y);
			for(String b:bankSet){
				if(in.containsKey(b)){
					objs=new Object[]{y,b,in.get(b)};
				}else{
					objs=new Object[]{y,b,0};
				}
				rus.add(objs);
			}
		}
		list=null;
		map=null;
		objs=null;
		in=null;
		return rus;
	}

	
	/**
	 * 各银行融资车台数分布
	 * @author xuxiaolong
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<String,Integer> findCarYear(Date startDate,Date endDate){
		
		StringBuilder sb = new StringBuilder("SELECT f._brief AS fin ,COUNT(*) AS counts ");
		sb.append(" FROM cm_certification_  cf , sys_financial_ f ");
		sb.append(" WHERE  cf._financial_id=f.id ");
		sb.append(" AND  cf._warehouseDate >=:startDate ");
		sb.append(" AND cf._warehouseDate <=:endDate ");
		sb.append(" AND cf._cmImporting!=2");
		sb.append(" GROUP BY cf._financial_id ");

		SQLQuery q=getSession().createSQLQuery(sb.toString());
		q.addScalar("fin", StringType.INSTANCE);
		q.addScalar("counts", IntegerType.INSTANCE);
		q.setDate("startDate", startDate);
		q.setDate("endDate", endDate);
		List<Object[]> list=q.list();
		
		Map<String,Integer> rus=new HashMap<String, Integer>();
		for(Object[] ary:list){
			if(ary.length==2){
				rus.put((String)ary[0], (Integer)ary[1]);
			}
		}
		return rus;
	}
}
